package com.boglond.util;

import com.boglond.entity.ColumnInfoDO;
import com.boglond.entity.TableInfoDO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;


public class TableUtil {
    private static final Logger log = LoggerFactory.getLogger(TableUtil.class);

    private static DatabaseMetaData DATABASE_META_DATA = DatabaseUtil.getDatabaseMetaData();
    private static String UNIFIED_TABLE_PREFIX = PropertyUtil.getValueByKey("unified.table.prefix");
    private static String DATABASE_INSTANCE = PropertyUtil.getValueByKey("database.instance");
    private static String TABLE_NAME = PropertyUtil.getValueByKey("unified.table.name");
    private static List<String> tableList;
    public static List<String> getAllTablesName(){
        tableList = new ArrayList<>();
        String[] types = { "TABLE" };
        try {
            ResultSet rs = DATABASE_META_DATA.getTables(DATABASE_INSTANCE, null, "%", types);
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");  //表名
                log.info(DATABASE_INSTANCE +"实例下的表:{}",tableName);
                tableList.add(tableName);
            }
        } catch (SQLException e) {
            log.error(">> TableUtil中获取指定实例下的所有表异常,详细信息:{}", e);
        }
        return tableList;
    }

    public static String getPrimaryKeyColumnNameByTable(String tableName){
        try {
            ResultSet resultSet = DATABASE_META_DATA.getPrimaryKeys(DATABASE_INSTANCE,null,tableName);
            while (resultSet.next()){
                log.info("PKTABLE_CAT:{}", resultSet.getString(1));
                log.info("PKTABLE_SCHEM:{}", resultSet.getString(2));
                log.info("PKTABLE_NAME:{}", resultSet.getString(3));
                log.info("COLUMN_NAME:{}", resultSet.getString(4));
                log.info("KEY_SEQ:{}", resultSet.getString(5));
                log.info("PK_NAME:{}", resultSet.getString(6));
                return resultSet.getString(4);
            }
        } catch (SQLException e) {
            log.error(">> TableUtil中获取指定表的主键异常,详细信息:{}", e);
        }
        return null;
    }

    public static String getPrimaryKeyColumnTypeByTableAndPrimaryKey(String tableName,String primaryKeyName){
        try {
            ResultSet rsColumns = DATABASE_META_DATA.
                    getColumns(DATABASE_INSTANCE,null,tableName,"%");
            while (rsColumns.next()){
                String columnName = rsColumns.getString("COLUMN_NAME");//列名
                if(primaryKeyName.equalsIgnoreCase(columnName)){
                    return rsColumns.getString("TYPE_NAME");
                }
            }
        } catch (SQLException e) {
            log.error(">> TableUtil中获取指定表的主键类型异常,详细信息:{}", e);
        }
        return null;
    }

    public static List<ColumnInfoDO> getColumnsInfoByTable(String tableName){

        List<ColumnInfoDO> columnsInfoDOList = new ArrayList<>();
        ColumnInfoDO columnInfoDO = null;
        try {
            ResultSet rsColumns = DATABASE_META_DATA.
                    getColumns(DATABASE_INSTANCE,null,tableName,"%");
            while (rsColumns.next()){
                columnInfoDO = new ColumnInfoDO();
                //列名
                String columnName = rsColumns.getString("COLUMN_NAME");
                columnInfoDO.setColumnName(columnName);
                //驼峰列名
                String[] columnNameStrArr = columnName.split("_");
                int size = columnNameStrArr.length;
                StringBuffer sb = new StringBuffer();
                for(int i = 0; i < size; i++){
                    if(size == 1){
                        sb.append(columnNameStrArr[0]);
                    }else{
                        if(i >= 1){
                            sb.append(columnNameStrArr[i].substring(0,1).toUpperCase()).
                                    append(columnNameStrArr[i].substring(1,columnNameStrArr[i].length()));
                        }else{
                            sb.append(columnNameStrArr[0]);
                        }
                    }
                }
                columnInfoDO.setHumpColumnName(sb.toString());

                int dataType = rsColumns.getInt("DATA_TYPE");
                columnInfoDO.setHumpColumnNameType(DateTypeUtil.getValueByKey(String.valueOf(dataType)));
                columnInfoDO.setDataType(dataType);
                String dataTypeName = rsColumns.getString("TYPE_NAME");
                if(dataType == 4){
                    columnInfoDO.setDataTypeName("INTEGER");
                }else if(dataType == 93){
                    columnInfoDO.setDataTypeName("TIMESTAMP");
                }else if(dataType == -1){
                    columnInfoDO.setDataTypeName("LONGVARCHAR");
                }else{
                    columnInfoDO.setDataTypeName(dataTypeName);
                }

                int columnSize = rsColumns.getInt("COLUMN_SIZE");//列大小
                columnInfoDO.setColumnSize(columnSize);
                int decimalDigits = rsColumns.getInt("DECIMAL_DIGITS");//小数位数
                columnInfoDO.setDecimalDigits(decimalDigits);
                int numPrecRadix = rsColumns.getInt("NUM_PREC_RADIX");//基数（通常是10或2）
                columnInfoDO.setNumPrecRadix(numPrecRadix);
                int nullAble = rsColumns.getInt("NULLABLE");//是否允许为null
                columnInfoDO.setNullAble(nullAble);
                String remarks = rsColumns.getString("REMARKS");//列描述
                columnInfoDO.setRemarks(remarks);
                String columnDef = rsColumns.getString("COLUMN_DEF");//默认值
                columnInfoDO.setColumnDef(columnDef);
                int sqlDataType = rsColumns.getInt("SQL_DATA_TYPE");//sql数据类型
                columnInfoDO.setSqlDataType(sqlDataType);
                int sqlDatetimeSub = rsColumns.getInt("SQL_DATETIME_SUB");   //SQL日期时间分?
                columnInfoDO.setSqlDatetimeSub(sqlDatetimeSub);
                int charOctetLength = rsColumns.getInt("CHAR_OCTET_LENGTH");   //char类型的列中的最大字节数
                columnInfoDO.setCharOctetLength(charOctetLength);
                int ordinalPosition = rsColumns.getInt("ORDINAL_POSITION");  //表中列的索引（从1开始）
                columnInfoDO.setOrdinalPosition(ordinalPosition);
                columnsInfoDOList.add(columnInfoDO);
            }
        } catch (SQLException e) {
            log.error(">> TableUtil中获取指定表下的的所有列信息异常,详细信息:{}", e);
        }
        return columnsInfoDOList;
    }

    public static List<TableInfoDO> getAllTableAndColumnsInfo(){
        List<TableInfoDO> tableInfoDOList = null;
        List<String> tableList = getAllTablesName();
        if(null == tableList || tableList.size() == 0){
            log.info(">> 存放表的list为空");
            return null;
        }

        tableInfoDOList = new ArrayList<>();
        TableInfoDO tableInfoDO = null;
        StringBuffer sb = null;
        List<ColumnInfoDO> columnInfoDOList = null;
        if (TABLE_NAME !=null && !TABLE_NAME.equals("")) {
            tableList = tableList.stream().filter(item -> item.equals(TABLE_NAME)).collect(Collectors.toList());
        }

        for(String table: tableList){
            log.info(">> 表名为:{}",table);
            tableInfoDO = new TableInfoDO();
            columnInfoDOList = TableUtil.getColumnsInfoByTable(table);
            tableInfoDO.setTableName(table);

            StringBuffer tableSb = new StringBuffer();
            StringBuffer urlSb = new StringBuffer();
            String upperNameOfTable = null;
            if(table.indexOf(UNIFIED_TABLE_PREFIX) == 0){
                String tableTmp = table.substring(UNIFIED_TABLE_PREFIX.length(),table.length());
                String[] strArr = tableTmp.split("_");
                for(String str: strArr){

                    tableSb.append(str.substring(0,1).toUpperCase()).
                            append(str.substring(1,str.length()));
                    urlSb.append(str).append("/");
                }
                upperNameOfTable = tableTmp.toUpperCase();
            } else {
                continue;
            }
            tableInfoDO.setUrl(urlSb.toString());
            tableInfoDO.setTableName(table);
            tableInfoDO.setUpperTableName(upperNameOfTable);
            tableInfoDO.setEntityName(tableSb.toString());
            String primaryKeyColumnName = getPrimaryKeyColumnNameByTable(table);
            tableInfoDO.setPrimaryKeyColumn(primaryKeyColumnName);
            StringBuffer primaryKeyName = new StringBuffer();
            String[] strArr = primaryKeyColumnName.split("_");
            int size = strArr.length;
            if(size > 1){
                for(int i = 0; i < size; i++){
                    if(i >= 1){
                        primaryKeyName.append(strArr[1].substring(0,1).toUpperCase()).
                                append(strArr[1].substring(1,strArr[1].length()));
                    }else{
                        primaryKeyName.append(strArr[0]);
                    }
                }
                tableInfoDO.setPrimaryKeyName(primaryKeyName.toString());
            }else{
                tableInfoDO.setPrimaryKeyName(primaryKeyColumnName);
            }

            tableInfoDO.setPrimaryKeyType(getPrimaryKeyColumnTypeByTableAndPrimaryKey(table,primaryKeyColumnName));
            tableInfoDO.setColumnsInfo(columnInfoDOList);
            sb = new StringBuffer();
            for(ColumnInfoDO obj: columnInfoDOList){
                sb.append(obj.getColumnName());
                sb.append(",");
            }
            String sbStr = sb.toString();
            tableInfoDO.setColumnsStr(sbStr.substring(0,sbStr.length() - 1));
            tableInfoDOList.add(tableInfoDO);
        }
        return tableInfoDOList;
    }

    public static void main(String[] args) {
        String s = "a,b,c,";
        //log.info(s.substring(0,s.length() - 1));
        String s1 = "uep_environment_type";
        //log.info(String.valueOf(s1.indexOf("_")));
        if(s1.indexOf("uep_") == 0){
            String strTmp = s1.substring(4,s1.length());
            String[] strArr = strTmp.split("_");
            StringBuffer sb = new StringBuffer();
            StringBuffer urlSb = new StringBuffer();
            for(String str: strArr){
                sb.append(str.substring(0,1).toUpperCase()).
                        append(str.substring(1,str.length())).toString();
                urlSb.append(str).append("/");
            }
            log.info("sb:{}", sb);
            log.info("urlSb:{}", urlSb);
            log.info("enumSb:{}", strTmp.toUpperCase());

        }

        String primaryKeyColumnName = getPrimaryKeyColumnNameByTable("uep_environment_type");
        log.info(">> 主键名称为:{}",primaryKeyColumnName);

    }
}
